<?php
namespace app\houtai\controller\user\room;
use app\houtai\controller\BaseController;
use think\Db;
use think\Request;

class CardController extends BaseController {
  /**
   *  @brief  : 用户管理-房卡消耗
   *  @return : page
   *  @details:
   */
  public function indexAction() {
    $conds = [];
    $order = [];
    $COST_TYPE = config('COST_TYPE');
    $CHANNELS   = $this->GetAdminChannel();
    $GAME_KIND  = GetGameKind();
    ////////////////////////////////////////////////////////////////////////////
    $Channel = $this->request->param('Channel/d', -1);
    if ($Channel > 0 && array_key_exists($Channel, $CHANNELS)) {
      $conds['b.Channel'][] = ['IN', array_keys($CHANNELS)];
      $conds['b.Channel'][] = ['EQ', $Channel];
    } else {
      $conds['b.Channel'] = ['IN', array_keys($CHANNELS)];
    }
    //////////////////////////////
    $KindID = $this->request->param('KindID/d', -1);
    if ($KindID > 0 && array_key_exists($KindID, $GAME_KIND)) {
      $conds['a.KindID'] = $KindID;
    }
    //////////////////////////////
    $time1 = $this->request->param('time1/s', '', 'trim');
    $time2 = $this->request->param('time2/s', '', 'trim');
    $aa = YmdHis2Unix($time1);
    $bb = YmdHis2Unix($time2);
    $ta = date('Y-m-d 00:00:00', $aa);
    $tb = date('Y-m-d 23:59:59', $bb);
    if ($aa > 0) {
      if ($bb > 0) {
        if ($aa <= $bb) {
          $conds['a.CostTime'] = ['BETWEEN TIME', [$ta, $tb]];
        } else {
          $conds['a.CostTime'] = ['>= TIME', $ta];
        }
      } else {
        $conds['a.CostTime'] = ['>= TIME', $ta];
      }
    } else {
      if ($bb > 0) {
        $conds['a.CostTime'] = ['<= TIME', $tb];
      }
    }
    //////////////////////////////
    $type = $this->request->param('type/d', -1);
    if ($type > 0 && array_key_exists($type, $COST_TYPE)) {
      $conds['a.dwCostType'] = $type;
    }
    //////////////////////////////
    $search = $this->request->param('search/s', '', 'trim');
    if (!empty($search)) {
      if (is_numeric($search) && $search > 0 && $search < PHP_INT_MAX) {
        $conds['b.UserID|b.GameID'] = $search;
      } else {
        $conds['b.NickName'] = ['LIKE', "%{$search}%"];
      }
    }
    ////////////////////////////////////////////////////////////////////////////
    $order_map = [
      'A' => 'RecordID',
      'B' => 'Channel',
      'C' => 'KindID',
      'D' => 'ServerID',
      'E' => 'GameID',
      'F' => 'dwCost',
      'G' => 'beleftRoomCard',
      'H' => 'dwCostType',
      'I' => 'CostTime',
    ];
    $this->orderField = array_key_exists($this->orderField, $order_map) ? $this->orderField : 'A';
    $order[$order_map[$this->orderField]] = $this->orderDirection;
    ////////////////////////////////////////////////////////////////////////////
    $list = table('CYRecordDB.dbo.CostRoomCardRecord a', 'rydb.record')->field([
      'a.RecordID', 'a.dwUserID', 'a.dwCost', 'a.dwCostType', 'a.beleftRoomCard', 'a.describe',
      'a.CostTime', 'b.UserID', 'b.GameID', 'b.Accounts', 'b.NickName', 'b.AccountsType',
      'b.LastLogonIP', 'b.RegisterIP', 'b.GameLogonCount', 'b.LastLogonDate', 'b.RegisterDate',
      'b.RegisterMobile', 'b.Compellation', 'b.PassPortID', 'b.AccountImgUrl', 'b.Sex',
      'b.Channel', 'a.KindID', 'a.ServerID', 'd.GameName',
    ])
    ->join('CYBasicDB.dbo.AccountsInfo b',   'b.UserID = a.dwUserID', 'LEFT')
    ->join('CYBasicDB.dbo.GameKindItem d','d.KindID = a.KindID', 'LEFT')
    ->where($conds)->order($order)
    ->paginate();

    $waste = table('CYRecordDB.dbo.CostRoomCardRecord a', 'rydb.record')->field([
      'ISNULL(SUM(CAST(a.dwCost AS BIGINT)), 0)' => 'dwCost',
    ])
    ->join('CYBasicDB.dbo.AccountsInfo b',   'b.UserID = a.dwUserID', 'LEFT')
    ->where($conds)->find();
    $waste = $waste['dwCost'];
    ////////////////////////////////////////////////////////////////////////////
    return $this->fetch('', [
      'list'  => $list,
      'search'=> $search,
      'type'  => $type,
      'time1' => $time1,
      'time2' => $time2,
      'waste' => $waste,
      'COST_TYPE' => $COST_TYPE,
      'CHANNELS'  => $CHANNELS,
      'GAME_KIND' => $GAME_KIND,
      'Channel'   => $Channel,
      'KindID'    => $KindID,
    ]);
  }
  /**
   *  @brief  : 用户管理-房卡消耗统计
   *  @return : page
   *  @details:
   */
  public function trendAction(){
    $conds = [];
    $order = [];
    $COST_TYPE = config('COST_TYPE');
    $CHANNELS = $this->GetAdminChannel();
    $GAME_KIND = GetGameKind();
    ////////////////////////////////////////////////////////////////////////////
    $Channel = $this->request->param('Channel/d', -1);
    if ($Channel > 0 && array_key_exists($Channel, $CHANNELS)) {
      $conds['d.Channel'][] = ['IN', array_keys($CHANNELS)];
      $conds['d.Channel'][] = ['EQ', $Channel];
    } else {
      $conds['d.Channel'] = ['IN', array_keys($CHANNELS)];
    }
    //////////////////////////////////////////
    $KindID = $this->request->param('KindID/d', -1);
    if ($KindID > 0 && array_key_exists($KindID, $GAME_KIND)) {
      $conds['a.KindID'] = $KindID;
    }
    //////////////////////////////////////////
    
    $type = $this->request->param('type/d', -1);
    $type = array_key_exists($type, $COST_TYPE) ? $type : -1;

    if ($type < 0) {
      $conds['a.dwCost'] = ['LT', 0];
    } else {
      $conds['a.dwCostType'] = $type;
      if (in_array($type, [7, 8, 9, 10])) {
        $conds['a.dwCost'] = ['GT', 0];
      } else {
        $conds['a.dwCost'] = ['LT', 0];
      }
    }
    $time1 = $this->request->param('time1/s', '', 'trim,YmdHis2Unix');
    $time2 = $this->request->param('time2/s', '', 'trim,YmdHis2Unix');
    $time2 = ($time2 > 0 && $time2 <= time()) ? $time2 : time();
    $time1 = ($time1 > 0 && $time1 <= $time2) ? $time1 : time() - 15 * 24 * 60 * 60; // 默认半个月的数据
    $time1 = date('Y-m-d 00:00:00', $time1);
    $time2 = date('Y-m-d 23:59:59', $time2);
    $conds['a.CostTime'] = ['BETWEEN TIME', [$time1, $time2]];
    ////////////////////////////////////////////////////////////////////////////
    $list = [];
    $sql2 = table('CYRecordDB.dbo.CostRoomCardRecord a', 'rydb.record')->field([
      'CONVERT(VARCHAR(10), [a].[CostTime], 120)' => 'y', 'COUNT(*)' => 'c',
    ])
    ->join('CYBasicDB.dbo.AccountsInfo d', 'd.UserID = a.dwUserID', 'LEFT')
    ->group('CONVERT(VARCHAR(10), [a].[CostTime], 120)')
    ->where($conds)->buildSql();

    $total = db('', 'rydb.record')->table("{$sql2} b")->count();  // 按日期分组满足条件有几条
    if($total>0) {
      $list = table('CYRecordDB.dbo.CostRoomCardRecord a', 'rydb.record')->field([
        'CONVERT(VARCHAR(10), [a].[CostTime], 120)' => 'y',
        'DATEPART(hh, [a].[CostTime])' => 'h',
        'SUM([a].[dwCost])' => 'c',
      ])
      ->join('CYBasicDB.dbo.AccountsInfo d', 'd.UserID = a.dwUserID', 'LEFT')
      ->group('CONVERT(VARCHAR(10), [a].[CostTime], 120), DATEPART(hh, [a].[CostTime])')
      ->where($conds)->order(['y' => 'DESC', 'h'])->select();
    }

    $path = Request::instance()->baseUrl();
    $page = new \org\dtcms($list, $total <= 0 ? 1 : $total, 1, $total, false, ['path' => $path]);
    ////////////////////////////////////////////////////////////////////////////
    $tree = [];
    $totalALL = 0;
    foreach($list as $a) {
      $y = $a['y'];
      if (!isset($tree[$y])) {
        $tree[$y] = ['total' => 0, 'child' => []];
      }
      $tree[$y]['total'] += $a['c'];
      $tree[$y]['child'][$a['h']] = $a;
      $totalALL += $a['c'];
    }
    ////////////////////////////////////////////////////////////////////////////
    return $this->fetch('', [
      'tree'      => $tree,
      'page'      => $page,
      'type'      => $type,
      'time1'     => $time1,
      'time2'     => $time2,
      'Channel'   => $Channel,
      'info'      => [
        'AvgDay'    => (count($tree)<=0 ? 0 : $totalALL/count($tree)),
        'TotalDay'  => count($tree),
        'TotalALL'  => $totalALL,
      ],
      'COST_TYPE' => $COST_TYPE,
      'CHANNELS'  => $CHANNELS,
      'GAME_KIND' => $GAME_KIND,
      'KindID'    => $KindID,
    ]);
  }


    /**
     *  @brief  :　房卡私人场
     *  @return : page
     *  @details:
     */
    public function privateAction() {
        $conds = [];
        $order = [];
        $COST_TYPE = config('COST_TYPE');
        $CHANNELS   = $this->GetAdminChannel();
        $GAME_KIND  = GetGameKind();
        ////////////////////////////////////////////////////////////////////////////
        $Channel = $this->request->param('Channel/d', -1);
        if ($Channel > 0 && array_key_exists($Channel, $CHANNELS)) {
            $conds['b.Channel'][] = ['IN', array_keys($CHANNELS)];
            $conds['b.Channel'][] = ['EQ', $Channel];
        } else {
            $conds['b.Channel'] = ['IN', array_keys($CHANNELS)];
        }
        //////////////////////////////
        $KindID = $this->request->param('KindID/d', -1);
        if ($KindID > 0 && array_key_exists($KindID, $GAME_KIND)) {
            $conds['a.KindID'] = $KindID;
        }
        //////////////////////////////
        $time1 = $this->request->param('time1/s', '', 'trim');
        $time2 = $this->request->param('time2/s', '', 'trim');
        $aa = YmdHis2Unix($time1);
        $bb = YmdHis2Unix($time2);
        $ta = date('Y-m-d 00:00:00', $aa);
        $tb = date('Y-m-d 23:59:59', $bb);
        if ($aa > 0) {
            if ($bb > 0) {
                if ($aa <= $bb) {
                    $conds['a.CostTime'] = ['BETWEEN TIME', [$ta, $tb]];
                } else {
                    $conds['a.CostTime'] = ['>= TIME', $ta];
                }
            } else {
                $conds['a.CostTime'] = ['>= TIME', $ta];
            }
        } else {
            if ($bb > 0) {
                $conds['a.CostTime'] = ['<= TIME', $tb];
            }
        }
        //////////////////////////////
        $type = $this->request->param('type/d', -1);
        if ($type > 0 && array_key_exists($type, $COST_TYPE)) {
            $conds['a.dwCostType'] = $type;
        }
        //////////////////////////////
        $search = $this->request->param('search/s', '', 'trim');
        if (!empty($search)) {
            if (is_numeric($search) && $search > 0 && $search < PHP_INT_MAX) {
                $conds['b.UserID|b.GameID'] = $search;
            } else {
                $conds['b.NickName'] = ['LIKE', "%{$search}%"];
            }
        }
        ////////////////////////////////////////////////////////////////////////////
        $order_map = [
            'A' => 'RecordID',
            'B' => 'Channel',
            'C' => 'KindID',
            'D' => 'ServerID',
            'E' => 'GameID',
            'F' => 'dwCost',
            'G' => 'beleftRoomCard',
            'H' => 'dwCostType',
            'I' => 'CostTime',
        ];
        $this->orderField = array_key_exists($this->orderField, $order_map) ? $this->orderField : 'A';
        $order[$order_map[$this->orderField]] = $this->orderDirection;
        ////////////////////////////////////////////////////////////////////////////
        $list = table('RYRecordDB.dbo.CostRoomCardRecord a', 'rydb.record')->field([
            'a.RecordID', 'a.dwUserID', 'a.dwCost', 'a.dwCostType', 'a.beleftRoomCard', 'a.describe',
            'a.CostTime', 'b.UserID', 'b.GameID', 'b.Accounts', 'b.NickName', 'b.AccountsType',
            'b.LastLogonIP', 'b.RegisterIP', 'b.GameLogonTimes', 'b.LastLogonDate', 'b.RegisterDate',
            'b.RegisterMobile'=>'MobilePhone', 'b.Compellation', 'b.PassPortID', 'b.AccountImgUrl'=>'HeadImgUrl', 'b.Sex'=>'Gender',
            'b.Channel', 'a.KindID', 'a.ServerID', 'd.GameName'=>'KindName',
        ])
            ->join('RYAccountsDB.dbo.AccountsInfo b',   'b.UserID = a.dwUserID', 'LEFT')
            ->join('RYPlatformDB.dbo.GameKindItem d','d.KindID = a.KindID', 'LEFT')
            ->where($conds)->order($order)
            ->paginate();

        $waste = table('CYRecordDB.dbo.CostRoomCardRecord a', 'rydb.record')->field([
            'ISNULL(SUM(CAST(a.dwCost AS BIGINT)), 0)' => 'dwCost',
        ])
            ->join('CYBasicDB.dbo.AccountsInfo b',   'b.UserID = a.dwUserID', 'LEFT')
            ->where($conds)->find();
        $waste = $waste['dwCost'];
        ////////////////////////////////////////////////////////////////////////////
        return $this->fetch('', [
            'list'  => $list,
            'search'=> $search,
            'type'  => $type,
            'time1' => $time1,
            'time2' => $time2,
            'waste' => $waste,
            'COST_TYPE' => $COST_TYPE,
            'CHANNELS'  => $CHANNELS,
            'GAME_KIND' => $GAME_KIND,
            'Channel'   => $Channel,
            'KindID'    => $KindID,
        ]);
    }
}
